Le I de ACID

AFUP Day Lyon

Qui suis-je?

Image by Anemone123 from Pixabay

Le I de ACID

  • ACID
  • Isolation des transactions
  • Cas d'usage
Image by Shameer Pk from Pixabay

ACID

  • Atomicité
  • Consistence
  • Isolation
  • Durabilité
Image by congerdesign from Pixabay

Atomicité

An atomic transaction is an indivisible and irreducible series of database operations such that either all occur, or nothing occurs.
Wikipedia
Image by MasterTux from Pixabay

Consistence

Consistency in database systems refers to the requirement that any given database transaction must change affected data only in allowed ways.
Wikipedia
Image by Magnascan from Pixabay

Isolation

In database systems, isolation determines how transaction integrity is visible to other users and systems.
Wikipedia

Durabilité

In database systems, durability is the ACID property which guarantees that transactions that have committed will survive permanently.
Wikipedia

Niveaux d'isolation

Image by Peter H from Pixabay

Read uncommitted

One transaction may see not-yet-committed changes made by other transactions.
Wikipedia
PostgreSQL's Read Uncommitted mode behaves like Read Committed.
Postgres documentation
Image by Peter H from Pixabay

Read uncommitted

If the transaction isolation level of the SQL-transaction is READ UNCOMMITTED, then any <level of isolation> is permissible.
SQL 2016 standard
Image by Peter H from Pixabay

Read committed

Read committed is an isolation level that guarantees that any data read is committed at the moment it is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read.
Wikipedia
Image by Peter H from Pixabay

Repeatable read

The Repeatable Read isolation level only sees data committed before the transaction began.
Postgres documentation
Image by Peter H from Pixabay

Serializable

This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently.
Postgres documentation
Image by Peter H from Pixabay


Anomalies d'isolation

Dirty read

A transaction reads data written by a concurrent uncommitted transaction.
Postgres documentation
Image from freepik

Dirty read

Image from freepik

Dirty read

Image from freepik

Dirty read

Image from freepik

Dirty read

Image from freepik

Lost update


A data integrity problem in which one writer of data overwrites the changes of a different writer modifying the same data.
Oracle documentation (Database Concepts)
Image by Pexels from Pixabay

Lost update


Image by Pexels from Pixabay

Lost update


Image by Pexels from Pixabay

Lost update


Image by Pexels from Pixabay

Lost update


Image by Pexels from Pixabay

Lost update


Image by Pexels from Pixabay

Non-repeatable read


A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).
Wikipedia
Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Non-repeatable read


Image by 자유 천지 from Pixabay

Phantom read

A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.
Postgres documentation
Image by Peter H from Pixabay

Phantom read

Image by Peter H from Pixabay

Phantom read

Image by Peter H from Pixabay

Phantom read

Image by Peter H from Pixabay

Serialization anomaly

The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.
Postgres documentation

Serialization anomaly

Serialization anomaly

Serialization anomaly

Serialization anomaly

Serialization anomaly

Serialization anomaly

Serialization anomaly

Anomalies and isolation levels

Isolation Level Read uncommitted Read committed Repeatable read Serializable
Dirty Read
Lost update
Non-repeatable Read
Phantom Read
Serialization Anomaly

Anomalies and isolation levels

(For Postgres)
Isolation Level Read uncommitted Read committed Repeatable read Serializable
Dirty Read
Lost update
Non-repeatable Read
Phantom Read
Serialization Anomaly

Locks

Record locking is the technique of preventing simultaneous access to data in a database, to prevent inconsistent results.
Wikipedia

Dead lock

A deadlock is a state in which each member of a group is waiting for another member, including itself, to take action, such as sending a message or more commonly releasing a lock.
Wikipedia

Dead lock

Dead lock

Dead lock

Dead lock

Dead lock

Et en pratique ?

    Est-ce grave d'avoir...
  • Dirty reads ?
  • Lost updates ?
  • Non-repeatable reads ?
  • Phantom reads ?
  • Serialization anomalies ?
Image by Boskampi from Pixabay

La plupart du temps

    Est-ce grave d'avoir...
  • Dirty reads ? Probably not
  • Lost updates ? Probably
  • Non-repeatable reads ? Probably
  • Phantom reads ? Probably
  • Serialization anomalies ? Probably
Image by Jakub Luksch from Pixabay

La plupart du temps

→ Read committed

Image by Jakub Luksch from Pixabay

Pagination

    Est-ce grave d'avoir...
  • Dirty reads ? Probably not
  • Lost updates ? Probably not
  • Non-repeatable reads ? It depends
  • Phantom reads ? Probably
  • Serialization anomalies ? Probably
Image by Linus Schütz from Pixabay

Pagination

→ Read committed or Repeatable read

Image by Linus Schütz from Pixabay

Gestion de stocks

    Est-ce grave d'avoir...
  • Dirty reads ? Probably not
  • Lost updates ? Probably not
  • Non-repeatable reads ? Probably not
  • Phantom reads ? Probably not
  • Serialization anomalies ? Probably
Image by Pexels from Pixabay

Gestion de stocks

→ Repeatable read

Image by Pexels from Pixabay

Numéros de factures

    Est-ce grave d'avoir...
  • Dirty reads ? Probably not
  • Lost updates ? Probably not
  • Non-repeatable reads ? Probably not
  • Phantom reads ? Probably not
  • Serialization anomalies ? Probably not

Numéros de factures

→ Serializable

Pour conclure

Image by Michitogo from Pixabay

Des questions ?

Image by Arek Socha from Pixabay